# -*- coding: utf-8 -*-
#
# Copyright 2017 zhangxp, st
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import datetime
import json

import os
import pickle
import re

from functools import wraps

from sqlalchemy import DATETIME, DATE, VARCHAR, create_engine
import numpy as np
import pandas as pd

START_DATE = 20050104
END_DATE = 29991231


BASE_DIR = r"C:\Users\admin\轮动(1)"

TABLE_NAME_MAP = {'INDX':'tb_index_1d', 'Future':'tb_future_1d'}


def dump_index_kline():
    """
    入库
    """
    mysql_connect = create_engine('mysql+pymysql://' + 'admin' + ':' + 'admin' + '@' + '192.168.12.110' + ':' + '3309' + '/' + 'wind' + '?charset=utf8')

    index_list = ["NH0100.NHF", "GTJAQH000.WI"]
    for index_id in index_list:
        df = pd.read_csv(r'C:\Users\admin\Documents\%s.csv' % index_id)
        #
        df["CODE"] = index_id
        print(df)
        try:
            df.to_sql('tb_index_1d', mysql_connect, if_exists='append', index=False, dtype={"CODE":VARCHAR(16), "AMT":VARCHAR(16), "DateTime":DATE})
        except Exception as e:
            if 'Duplicate' not in (e):
                raise e
    #
    mysql_connect.dispose()


def dump_future_index_kline():
    """
    入库
    """
    mysql_connect = create_engine('mysql+pymysql://' + 'admin' + ':' + 'admin' + '@' + '192.168.12.110' + ':' + '3309' + '/' + 'wind' + '?charset=utf8')

    symbols = os.listdir(r'C:\Users\admin\Downloads\FICC')

    for symbol in symbols:
        #
        df = pd.read_csv(r'C:\Users\admin\Downloads\FICC\%s' % symbol)
        #
        df["CODE"] = symbol[:-4]
        print(df)
        try:
            df.to_sql('tb_index_1d', mysql_connect, if_exists='append', index=False, dtype={"CODE":VARCHAR(16), "DateTime":DATE})
        except Exception as e:
            if 'Duplicate' not in (e):
                raise e
        # 构建自定义标的
        df = pd.DataFrame()
        df['code'] = [symbol[:-4]]
        df['display_name'] = '万得' + symbol[:-4]
        df['name'] = symbol[:-4]
        df['listed_date'] = '1991-04-03'
        df['de_listed_date'] = '2200-01-01'
        df['type'] = 'INDX'
        df.to_sql('tb_inst_info', mysql_connect, if_exists='append', index=False, dtype={"code":VARCHAR(16), "listed_date":DATE, "de_listed_date":DATE})
    #
    mysql_connect.dispose()


def dump_future_kline():
    """
    入库
    """
    mysql_connect = create_engine('mysql+pymysql://' + 'admin' + ':' + 'admin' + '@' + '192.168.12.110' + ':' + '3309' + '/' + 'wind' + '?charset=utf8')

    symbols = os.listdir(BASE_DIR + r'\Data\CTA')
    symbols = [i.split('.')[0] for i in symbols]

    import xlrd
    for symbol in symbols:
        future_codes = []

        data = xlrd.open_workbook(BASE_DIR + fr'\Data\CTA\{symbol}.xls')
        table = data.sheet_names()
        print({symbol.upper(): table})
        for name in table:
            symbol_name = f'{symbol.upper()}{name}'
            data2 = pd.read_excel(BASE_DIR + fr'\Data\CTA\{symbol}.xls', sheet_name=name, header=None)
            data2.columns = ['datetime', symbol_name]
            data2['DateTime'] = pd.to_datetime(data2['datetime'])
            data2["OPEN"] = data2["CLOSE"] = data2[symbol_name]
            data2["CODE"] = symbol_name
            data2 = data2[["CODE", "OPEN", "CLOSE", "DateTime"]]
            data2 = data2.drop_duplicates(subset='DateTime', keep='last')
            try:
                data2.to_sql('tb_future_1d', mysql_connect, if_exists='append', index=False, dtype={"CODE":VARCHAR(16), "AMT":VARCHAR(16), "DateTime":DATE})
            except Exception as e:
                if 'Duplicate' not in (e):
                    raise e
            future_codes.append(symbol_name)
        # 构建自定义标的
        df = pd.DataFrame()
        df['code'] = future_codes
        df['display_name'] = '自定义' + symbol
        df['name'] = symbol
        df['listed_date'] = '1991-04-03'
        df['de_listed_date'] = '2200-01-01'
        df['type'] = 'Future'
        df.to_sql('tb_inst_info', mysql_connect, if_exists='append', index=False, dtype={"CODE":VARCHAR(16), "listed_date":DATE, "de_listed_date":DATE})
    #
    mysql_connect.dispose()


def load_index_kline(code=None):
    """
    入库
    """
    mysql_connect = create_engine('mysql+pymysql://' + 'admin' + ':' + 'admin' + '@' + '192.168.12.110' + ':' + '3309' + '/' + 'wind' + '?charset=utf8')

    if code is None:
        df = pd.read_sql("SELECT DateTime,OPEN,CLOSE,HIGH,LOW,VOLUME,AMT FROM tb_index_1d",  mysql_connect, parse_dates=["DateTime"])
    else:
        df = pd.read_sql("SELECT DateTime,OPEN,CLOSE,HIGH,LOW,VOLUME,AMT FROM tb_index_1d where CODE='%s'" % code,  mysql_connect, parse_dates=["DateTime"])
    #
    mysql_connect.dispose()

    return df


def load_inst_kline(type, code=None):
    """
    入库
    """
    mysql_connect = create_engine('mysql+pymysql://' + 'admin' + ':' + 'admin' + '@' + '192.168.12.110' + ':' + '3309' + '/' + 'wind' + '?charset=utf8')

    table_name = TABLE_NAME_MAP[type]
    if code is None:
        df = pd.read_sql(f"SELECT DateTime,OPEN,CLOSE,HIGH,LOW,VOLUME,AMT FROM {table_name}",  mysql_connect, parse_dates=["DateTime"])
    else:
        df = pd.read_sql(f"SELECT DateTime,OPEN,CLOSE,HIGH,LOW,VOLUME,AMT FROM {table_name} where CODE='{code}'",  mysql_connect, parse_dates=["DateTime"])
    #
    mysql_connect.dispose()

    return df

def load_inst_info(type=None):
    """
    入库
    """
    mysql_connect = create_engine('mysql+pymysql://' + 'admin' + ':' + 'admin' + '@' + '192.168.12.110' + ':' + '3309' + '/' + 'wind' + '?charset=utf8')


    if type is None:
        df = pd.read_sql("SELECT code,display_name,name,listed_date,de_listed_date,type FROM tb_inst_info",  mysql_connect, parse_dates=["DateTime"])
    else:
        df = pd.read_sql("SELECT code,display_name,name,listed_date,de_listed_date,type FROM tb_inst_info where type='%s'" % type,  mysql_connect, parse_dates=["DateTime"])
    #
    mysql_connect.dispose()

    return df


def update_database():
    pass


if __name__ == '__main__':
    dump_future_index_kline()
    pass
